﻿--use PolePosition
GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'up_DistributionInspVisitMeterToDistributionMeterAsset')
	DROP PROCEDURE up_DistributionInspVisitMeterToDistributionMeterAsset
GO


CREATE PROCEDURE [dbo].[up_DistributionInspVisitMeterToDistributionMeterAsset]
(
	@vendorProjectId int
)
AS

/*
SELECT * FROM tb_Utility_Asset_Meters

DELETE tb_Utility_Asset_Meters_Images
DELETE tb_Utility_Asset_Meters_Notes
DELETE tb_Utility_Asset_Meters
UPDATE tb_Vendor_Distribution_Meters SET utility_asset_meters_meterfk = NULL

DBCC CHECKIDENT (tb_Utility_Asset_Meters, RESEED, 0)
*/


--Get Ids
DECLARE @utilityId int
SELECT 
	@utilityId = UtilityFk
FROM
	tb_Vendor_projects
WHERE
	Vendor_ProjectId = @vendorProjectId

DECLARE @UtilityProjectId int
SELECT 
	@UtilityProjectId = uProject.Utility_ProjectId
FROM 
	tb_vendor_Projects vProject
INNER JOIN tb_utility_projects  uProject
	ON vProject.VendorFk = uProject.VendorFk
	AND vProject.ProjectTypeFk = uProject.TypeFk
WHERE  
	vProject.Vendor_ProjectId = @vendorProjectId

DECLARE @insertedAssetIds TABLE (Utility_Asset_MeterId int, MeterId int)

--Inserts asset meters
INSERT INTO tb_Utility_Asset_Meters
(
	UtilityFK,
	Latitude,
	Longitude,
	Altitude,
	SatelliteCount,
	SatelliteTime,
	SolutionAvailable,
	HandHeld,
	Meter_TopTypeFk,
	Utility_ContactFk,
	Date,
	Utility_ProjectFk,
	MapFk,
	SectionFk,
	Number,
	Comments
)
OUTPUT INSERTED.Utility_Asset_MeterId, INSERTED.Altitude INTO @insertedAssetIds
SELECT
	@utilityId,
	meters.Latitude,
	meters.Longitude,
	meters.Vendor_Distribution_Meters_MeterId, -- TODO: Change this, Using Altitude as temp id
	NULL, 
	NULL,
	0,
	NULL,
	NULL,
	NULL,
	poles.AcquisitionDate,
	@UtilityProjectId,
	poles.MapFk,
	poles.SectionFk,
	meters.Number,
	meters.Comments
FROM
	tb_Vendor_Distribution_Poles poles
INNER JOIN tb_Vendor_Distribution_Meters meters
	ON meters.Vendor_Distribution_PoleFk = poles.Vendor_Distribution_Poles_PoleId
WHERE
	poles.ProjectFk = @vendorProjectId
	AND meters.Utility_Asset_Meters_MeterFk IS NULL


--Insert meter images
INSERT INTO tb_Utility_Asset_Meters_Images
(
	Utility_Asset_MeterFK,
	Direction,
	Latitude,
	Longitude,
	Altitude,
	SatelliteCount,
	SatelliteTime,
	SolutionAvailable,
	[FileName]
)
SELECT
	[InsertedIds].Utility_Asset_MeterId,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	0,
	meters.ImageFileName
FROM
	@insertedAssetIds [InsertedIds]
INNER JOIN tb_Vendor_Distribution_Meters meters
	ON meters.Vendor_Distribution_Meters_MeterId = [InsertedIds].MeterId
WHERE
	meters.ImageFileName IS NOT NULL

--Insert meter notes
INSERT INTO tb_Utility_Asset_Meters_Notes
(
	Utility_Asset_MeterFK,
	Utility_Asset_MeterNoteFK
)
SELECT 
	[assets].Utility_Asset_MeterId,
	notesxMeter.Vendor_Distribution_NoteFk
FROM
	@insertedAssetIds [assets]
INNER JOIN tb_Vendor_Distribution_NotesXMeter notesxMeter
	ON [assets].MeterId = notesxMeter.Vendor_Distribution_MeterFk


--Update inspection meters to relate them to the asset
UPDATE 
	tb_Vendor_Distribution_Meters 
SET
	Utility_Asset_Meters_MeterFk = [InsertedIds].Utility_Asset_MeterId
FROM 
	@insertedAssetIds [InsertedIds]
WHERE
	tb_Vendor_Distribution_Meters.Vendor_Distribution_Meters_MeterId = [InsertedIds].MeterId


--Update Altitude to NULL
UPDATE 
	tb_Utility_Asset_Meters
SET
	Altitude = NULL
FROM 
	tb_Utility_Asset_Meters
INNER JOIN @insertedAssetIds [InsertedIds]
	ON tb_Utility_Asset_Meters.Utility_Asset_MeterId = [InsertedIds].Utility_Asset_MeterId

/*
select * from tb_Utility_Asset_Meters_Images
*/ 

GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'DevOnly_DistributionInspVisitMeterToAsset_UnitTestInitialize')
	DROP PROCEDURE DevOnly_DistributionInspVisitMeterToAsset_UnitTestInitialize
GO

CREATE proc [dbo].[DevOnly_DistributionInspVisitMeterToAsset_UnitTestInitialize]
(
	@dummyUtilityId int output,
	@dummyVendorId int output
)
AS
SET NOCOUNT ON	

--Creates dummy utility
INSERT INTO tb_Utilities VALUES ('Dummy Utility', 1, 1)
SELECT @dummyUtilityId = @@IDENTITY

--Creates dummy vendor
INSERT INTO tb_Vendors VALUES ('Dummy Vendor', 2, 1)
SELECT @dummyVendorId = @@IDENTITY

--Creates dummy vendor project
INSERT INTO tb_vendor_projects 
(VendorFk, Name, ProjectTypeFk, UtilityFk, ScheduledStartDate, ActualStartDate, ScheduledEndDate, ScheduledCost, ActualCost, TypeFk)
VALUES (@dummyVendorId, 'Dummy distribution project', 3, @dummyUtilityId, GETDATE(), GETDATE(), GETDATE(), 0, 0, 3)

--Creates dummy users
INSERT INTO tb_vendor_contacts VALUES (@dummyVendorId, 1, 'Dummy name', 'dummy.name', 1, 'password', 1)
INSERT INTO tb_utility_contacts VALUES (@dummyUtilityId, 1, 'Dummy name', 'dummy.name', 1, 'password', 1)

--Gets ids 
DECLARE @vendorContactId int
DECLARE @vendorProjectId int

SELECT 
	@vendorContactId = Vendor_ContactId
FROM 
	tb_vendor_contacts
WHERE
	vendorFk = @dummyVendorId

SELECT 
	@vendorProjectId = Vendor_ProjectID
FROM 
	tb_vendor_projects
WHERE
	vendorFk = @dummyVendorId

--Select 20 distribution poles from and set as dummy poles
DECLARE @insertedPolesIds TABLE (newVendorDistributionPoleId int)

INSERT INTO tb_Vendor_Distribution_Poles
(
	AcquisitionDate
	,VendorFk
	,InspectorFk
	,ProjectFk
	,Latitude
	,Longitude
)
OUTPUT INSERTED.Vendor_Distribution_Poles_PoleId INTO @insertedPolesIds
SELECT TOP 20 
	AcquisitionDate
	,@dummyVendorId
	,@vendorContactId
	,@vendorProjectId
	,Latitude
	,Longitude
FROM
	tb_Vendor_Distribution_Poles
ORDER BY 
	1 DESC

INSERT INTO tb_Vendor_Distribution_Meters
(
	Vendor_Distribution_PoleFk
)
SELECT 
	newVendorDistributionPoleId
FROM 
	@insertedPolesIds 

GO


IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'DevOnly_DistributionInspVisitMeterToAsset_UnitTestCleanUp')
	DROP PROCEDURE DevOnly_DistributionInspVisitMeterToAsset_UnitTestCleanUp
GO

CREATE proc [dbo].[DevOnly_DistributionInspVisitMeterToAsset_UnitTestCleanUp]
(
	@dummyUtilityId int,
	@dummyVendorId int
)
AS
/* 	
	Purpose: Clears tables used to test 
*/

SET NOCOUNT ON	

--Delete Distribution meters
DELETE tb_Vendor_Distribution_Meters
WHERE Vendor_Distribution_PoleFk IN 
	(
		SELECT poles.Vendor_Distribution_Poles_PoleId
		FROM tb_Vendor_Distribution_Poles poles
		INNER JOIN tb_Vendor_Projects projects
			ON poles.ProjectFk = projects.Vendor_ProjectId
		WHERE
			projects.VendorFk = @dummyVendorId
	)

--Delete Distribution poles
DELETE tb_Vendor_Distribution_Poles
WHERE ProjectFk IN (SELECT Vendor_ProjectId FROM tb_Vendor_Projects WHERE VendorFk = @dummyVendorId)

--Delete Distribution meters assets
DELETE tb_Utility_Asset_Meters
WHERE UtilityFk = @dummyUtilityId

--Delete Distribution assets
DELETE tb_Utility_Asset_Distributions
WHERE UtilityFk = @dummyUtilityId

--Delete users
DELETE tb_vendor_contacts 
WHERE VendorFk = @dummyVendorId

DELETE tb_utility_contacts 
WHERE UtilityFk = @dummyUtilityId

--Delete the project
DELETE tb_vendor_projects
WHERE VendorFk = @dummyVendorId

--Deletes the utility
DELETE tb_Utilities 
WHERE UtilityId = @dummyUtilityId

--Deletes the vendor
DELETE tb_Vendors
WHERE VendorId = @dummyVendorId

GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'DevOnly_DistributionInspVisitMeterToAsset_UnitTest')
	DROP PROCEDURE DevOnly_DistributionInspVisitMeterToAsset_UnitTest
GO

CREATE proc [dbo].[DevOnly_DistributionInspVisitMeterToAsset_UnitTest] as
/* 	
	Purpose: Tests stored procedures 
*/
SET NOCOUNT ON	


DECLARE @dummyUtilityId int
DECLARE @dummyVendorId int
DECLARE @x varchar(100)
DECLARE @y varchar(100)

--Initialize data to test
PRINT 'Initializing data to test'
EXEC DevOnly_DistributionInspVisitMeterToAsset_UnitTestInitialize @dummyUtilityId OUTPUT, @dummyVendorId OUTPUT

PRINT 'Dummy utility ID: ' + CONVERT(varchar, @dummyUtilityId)
PRINT 'Dummy vendor ID: ' + CONVERT(varchar, @dummyVendorId)

-- bail on error
if (NOT @@error = 0)
	return(1)

--Executes tests
DECLARE @vendorProjectId int
SELECT @vendorProjectId = Vendor_ProjectId FROM tb_vendor_projects WHERE UtilityFk = @dummyUtilityId

--Gets count of assets that should be created
DECLARE @assetsCount int
DECLARE @metersCount int

SELECT @metersCount = COUNT(*) 
FROM tb_Vendor_Distribution_Meters meters
INNER JOIN tb_Vendor_Distribution_Poles poles
	ON meters.Vendor_Distribution_PoleFk = poles.Vendor_Distribution_Poles_PoleId
WHERE poles.ProjectFK = @vendorProjectId

--Create assets from visits
EXEC up_DistributionInspVisitMeterToDistributionMeterAsset @vendorProjectId, 20

SELECT @assetsCount = COUNT(*) FROM tb_Utility_Asset_Meters WHERE UtilityFk = @dummyUtilityId

if (@assetsCount <> @metersCount)
begin
	set @x = 'Assets created: ' + convert(varchar(100), @assetsCount)
	set @y = 'Meters should be converted: ' + convert(varchar(100), @metersCount)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'Meters used and assets created should be equal.')
end

SELECT @metersCount = COUNT(*) 
FROM tb_Vendor_Distribution_Meters meters
INNER JOIN tb_Vendor_Distribution_Poles poles
	ON meters.Vendor_Distribution_PoleFk = poles.Vendor_Distribution_Poles_PoleId
WHERE poles.ProjectFK = @vendorProjectId
AND meters.Utility_Asset_Meters_MeterFk = NULL

if (@metersCount <> 0)
begin
	set @y = convert(varchar(100), @metersCount)
	set @x = convert(varchar(100), 0)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'Problem in meter asset foreign key, it shouldnt be null')
end

--If I execute it again no assets should be created
--Gets poles converted to compare later
DECLARE @metersConverted table (idMeter int, idAsset int)

INSERT INTO @metersConverted
(
	idMeter,
	idAsset
)
SELECT meters.Vendor_Distribution_Meters_MeterId, meters.Utility_Asset_Meters_MeterFk
FROM tb_Vendor_Distribution_Meters meters
INNER JOIN tb_Vendor_Distribution_Poles poles
	ON meters.Vendor_Distribution_PoleFk = poles.Vendor_Distribution_Poles_PoleId
WHERE poles.ProjectFK = @vendorProjectId 
AND meters.Utility_Asset_Meters_MeterFk IS NOT NULL

EXEC up_DistributionInspVisitMeterToDistributionMeterAsset @vendorProjectId, 20

SELECT @metersCount = COUNT(*)
FROM tb_Vendor_Distribution_Meters meters
INNER JOIN @metersConverted metersConverted
	ON meters.Vendor_Distribution_Meters_MeterId = metersConverted.idMeter
	AND meters.Utility_Asset_Meters_MeterFk = metersConverted.idAsset

DECLARE @originalPoles int

SELECT @originalPoles = COUNT(*) FROM @metersConverted

if (@originalPoles <> @metersCount)
begin
	set @y = convert(varchar(100), @metersCount)
	set @x = convert(varchar(100), @originalPoles)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'New Assets where created. It shouldnt happened')
end

--Deletes data used
PRINT 'Deleting data used to test'
EXEC DevOnly_DistributionInspVisitMeterToAsset_UnitTestCleanUp @dummyUtilityId, @dummyVendorId

-- bail on error
if (NOT @@error = 0)
	return(1)



GO